package cn.zy.management;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DBUtil {

	public DBUtil() throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		connection = DriverManager.getConnection(url, username, password);
	}
	
	public List<String> getNameFromIPAddress(String ipAddress) throws SQLException {
		ArrayList<String> nameList = new ArrayList<String>();
		String name = null;
		Statement stmt = connection.createStatement();
		ResultSet result = stmt.executeQuery("select name from " + tablename + " where ip = '" + ipAddress +"'");
		
		while (result.next()) {
			name = result.getString("name");
			nameList.add(name);
		}
		
		return nameList;
	}
	
	public List<String> getIPAddressFromName(String name) throws SQLException {
		ArrayList<String> addressList = new ArrayList<String>();
		String address = null;
		Statement stmt = connection.createStatement();
		ResultSet result = stmt.executeQuery("select ip from " + tablename + " where name = '" + name + "'");
		
		while (result.next()) {
			address = result.getString("ip");
			addressList.add(address);
		}
		
		return addressList;
	}
	
	public List<String> getAllNames() throws SQLException {
		ArrayList<String> allNameList = new ArrayList<String>();
		String name = null;
		Statement stmt = connection.createStatement();
		ResultSet result = stmt.executeQuery("select distinct name from " + tablename);
		
		while (result.next()) {
			name = result.getString("name");
			allNameList.add(name);
		}
		
		return allNameList;
	}
	
	public List<String> getAllIPAddresses() throws SQLException {
		ArrayList<String> allIPList = new ArrayList<String>();
		String ip = null;
		Statement stmt = connection.createStatement();
		ResultSet result = stmt.executeQuery("select distinct ip from " + tablename);
		
		while (result.next()) {
			ip = result.getString("ip");
			allIPList.add(ip);
		}
		
		return allIPList;
	}
	
	public StringPair getAllNameAndIP() throws SQLException {
		StringPair pair = new StringPair();
		String name = null;
		String ip = null;
		Statement stmt = connection.createStatement();
		ResultSet result = stmt.executeQuery("select name, ip from " + tablename);
		
		while (result.next()) {
			name = result.getString("name");
			ip = result.getString("ip");
			
			pair.put(name, ip);
		}
		
		return pair;
	}
	
	private String url = "jdbc:mysql://localhost:3306/test";
	private String username = "root";
	private String password = "root";
	private String tablename = "user";
	private Connection connection = null;
}
